Calling Stored Procedures Using CallableStatement in JDBC

Course: Advanced Java Training

Topic: Calling Stored Procedures Using CallableStatement

Course Link: Advanced Java Training Course

Demo Link: Register for a Course Demo

Website: JustAcademy Official Website

1. Introduction to Stored Procedures in JDBC

A stored procedure is a group of SQL statements stored in a database and executed when an application calls it. Stored procedures can perform operations such as inserting records, retrieving information, updating data, validating business rules, and processing transactions.

Java Database Connectivity (JDBC) provides the CallableStatement interface for calling stored procedures from Java applications. It allows Java programs to supply input parameters, register output parameters, execute procedures, and retrieve returned values or result sets.

For example, an employee management application might call a stored procedure to:

• Add a new employee to the database.
• Find an employee using an ID.
• Update an employee's salary.
• Return the total number of employees in a department.
• Execute multiple database operations using predefined database logic.

2. Learning Objectives

After completing this topic, you will be able to:

• Understand stored procedures and their benefits.
• Explain the purpose of the CallableStatement interface.
• Create stored procedures using SQL.
• Call stored procedures from Java using JDBC.
• Pass IN parameters and retrieve OUT parameters.
• Work with INOUT parameters.
• Retrieve result sets returned by stored procedures.
• Handle SQL exceptions and manage JDBC resources correctly.
• Apply security and performance best practices when using stored procedures.

3. What Is CallableStatement?

CallableStatement is an interface in the java.sql package. It extends PreparedStatement and is specifically designed to invoke stored procedures and, where supported, stored functions.

A CallableStatement is created through the prepareCall() method of a JDBC Connection object.

CallableStatement statement =
    connection.prepareCall("{call procedure_name(?)}");

The question mark represents a parameter placeholder. The application supplies values for input parameters and registers output parameters before executing the call.

4. Understanding Stored Procedures

A stored procedure is defined in the database using the syntax supported by the database management system. The exact syntax can differ between MySQL, Oracle Database, PostgreSQL, SQL Server, and other systems.

4.1 Example of a Stored Procedure

The following MySQL procedure retrieves an employee by ID:

DELIMITER //

CREATE PROCEDURE GetEmployeeById(IN p_employee_id INT)
BEGIN
    SELECT id, name, email, department, salary
    FROM employees
    WHERE id = p_employee_id;
END //

DELIMITER ;

In this example:

• GetEmployeeById is the procedure name.
• IN p_employee_id INT defines an input parameter.
• The SELECT statement retrieves the matching employee.
• The result is returned as a result set that the JDBC application can process.

The DELIMITER command is used by MySQL command-line clients and some SQL tools to change how they recognize the end of a statement. It is a client command, not part of the stored procedure definition sent by a typical JDBC application.

5. Parameter Types in Stored Procedures

Stored procedures commonly use three parameter modes: IN, OUT, and INOUT.

5.1 IN Parameter

An IN parameter provides a value to the procedure. In Java, set it using an appropriate setter method such as setInt(), setString(), or setBigDecimal().

statement.setInt(1, 10);

5.2 OUT Parameter

An OUT parameter returns a value from the procedure. Before execution, register it with registerOutParameter().

statement.registerOutParameter(2, java.sql.Types.VARCHAR);

After execution, retrieve the output using the appropriate getter method, such as getString() or getInt().

5.3 INOUT Parameter

An INOUT parameter accepts an initial value and can return a modified value. In JDBC, set the input value and register the output type.

statement.setBigDecimal(
    1, new java.math.BigDecimal("100.00")
);
statement.registerOutParameter(
    1, java.sql.Types.DECIMAL
);

For an INOUT parameter, the same parameter index is used to set the input and register the output.

6. Difference Between Statement, PreparedStatement, and CallableStatement

7. JDBC Workflow for Calling a Stored Procedure

1. Establish a database connection.
2. Create a CallableStatement using Connection.prepareCall().
3. Set the input parameter values.
4. Register output parameters, if required.
5. Execute the stored procedure.
6. Retrieve output values or process result sets.
7. Close the result sets and statement.
8. Close the connection when it is no longer needed.

Java Application
       |
       v
Connection.prepareCall()
       |
       v
CallableStatement
       |
       v
Set IN Parameters
       |
       v
Register OUT Parameters
       |
       v
Execute Stored Procedure
       |
       +---- OUT Values
       |
       +---- ResultSet
       |
       v
Process Results and Close Resources

8. Preparing the Database

The following examples use MySQL and an employee table. If you already created the database in an earlier JDBC lesson, you can reuse it.

CREATE DATABASE IF NOT EXISTS company_db;

USE company_db;

CREATE TABLE IF NOT EXISTS employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    department VARCHAR(100),
    salary DECIMAL(10, 2) NOT NULL
);

Insert sample records if the table is empty:

INSERT INTO employees (name, email, department, salary)
VALUES
('Amit Sharma', '[email protected]', 'IT', 45000.00),
('Neha Patil', '[email protected]', 'HR', 42000.00),
('Rahul Verma', '[email protected]', 'Finance', 50000.00);

9. Creating a Reusable Database Connection Class

Use a connection utility to avoid repeating connection configuration in every example. This class reads credentials from environment variables rather than embedding passwords in source code.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public final class DBConnection {
    private static final String URL =
        "jdbc:mysql://localhost:3306/company_db";

    private DBConnection() {
    }

    public static Connection getConnection()
            throws SQLException {

        String user = System.getenv("DB_USER");
        String password = System.getenv("DB_PASSWORD");

        if (user == null || password == null) {
            throw new SQLException(
                "Configure DB_USER and DB_PASSWORD."
            );
        }

        return DriverManager.getConnection(URL, user, password);
    }
}

Ensure that the MySQL Connector/J dependency is included in your project and that the environment variables contain valid database credentials.

10. Calling a Procedure with an IN Parameter

This example calls GetEmployeeById and retrieves the employee record matching a supplied ID.

10.1 Stored Procedure

DELIMITER //

CREATE PROCEDURE GetEmployeeById(IN p_employee_id INT)
BEGIN
    SELECT id, name, email, department, salary
    FROM employees
    WHERE id = p_employee_id;
END //

DELIMITER ;

If the procedure already exists, drop it before recreating it when appropriate:

DROP PROCEDURE IF EXISTS GetEmployeeById;

10.2 Java CallableStatement Example

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

public class CallEmployeeById {
    public static void main(String[] args) {
        String call = "{call GetEmployeeById(?)}";

        try (Connection connection = DBConnection.getConnection();
             CallableStatement statement =
                 connection.prepareCall(call)) {

            statement.setInt(1, 1);

            try (ResultSet resultSet = statement.executeQuery()) {
                if (resultSet.next()) {
                    System.out.println(
                        "ID: " + resultSet.getInt("id")
                    );
                    System.out.println(
                        "Name: " + resultSet.getString("name")
                    );
                    System.out.println(
                        "Email: " + resultSet.getString("email")
                    );
                    System.out.println(
                        "Department: " +
                        resultSet.getString("department")
                    );
                    System.out.println(
                        "Salary: " +
                        resultSet.getBigDecimal("salary")
                    );
                } else {
                    System.out.println("Employee not found.");
                }
            }

        } catch (SQLException e) {
            System.err.println("Stored procedure call failed.");
            e.printStackTrace();
        }
    }
}

10.3 Explanation

• {call GetEmployeeById(?)} is JDBC escape syntax for a procedure call.
• prepareCall() creates the CallableStatement.
• setInt(1, 1) sets the first parameter to employee ID 1.
• executeQuery() is used here because the procedure returns a result set.
• ResultSet provides access to the returned employee data.

11. Calling a Procedure with an OUT Parameter

An OUT parameter allows a stored procedure to return a single value through its parameter list. For this example, the procedure returns an employee's name for a given employee ID.

11.1 Create the Procedure

DELIMITER //

CREATE PROCEDURE GetEmployeeName(
    IN p_employee_id INT,
    OUT p_employee_name VARCHAR(100)
)
BEGIN
    SELECT name
    INTO p_employee_name
    FROM employees
    WHERE id = p_employee_id;
END //

DELIMITER ;

If no employee matches the ID, the selected output value may be NULL. Applications should handle that possibility.

11.2 Java Example

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;

public class EmployeeNameProcedure {
    public static void main(String[] args) {
        String call = "{call GetEmployeeName(?, ?)}";

        try (Connection connection = DBConnection.getConnection();
             CallableStatement statement =
                 connection.prepareCall(call)) {

            statement.setInt(1, 1);
            statement.registerOutParameter(2, Types.VARCHAR);

            statement.execute();

            String employeeName = statement.getString(2);

            if (employeeName == null) {
                System.out.println("No employee name was returned.");
            } else {
                System.out.println("Employee name: " + employeeName);
            }

        } catch (SQLException e) {
            System.err.println("Unable to retrieve employee name.");
            e.printStackTrace();
        }
    }
}

11.3 Explanation

1. Set the input employee ID using setInt().
2. Register the second parameter as a VARCHAR output using registerOutParameter().
3. Execute the stored procedure using execute().
4. Read the returned value using getString(2).

Register each OUT parameter before executing the procedure. Use a JDBC type that matches the database procedure's declared output type.

12. Calling a Procedure with an INOUT Parameter

An INOUT parameter receives an initial value, performs processing, and returns an updated value. The following MySQL procedure calculates a proposed salary increase while limiting the maximum raise percentage.

12.1 Create the Procedure

DELIMITER //

CREATE PROCEDURE CalculateSalaryRaise(
    IN p_employee_id INT,
    IN p_max_raise_percentage DECIMAL(5, 2),
    INOUT p_new_salary DECIMAL(10, 2)
)
BEGIN
    DECLARE v_current_salary DECIMAL(10, 2);
    DECLARE v_max_salary DECIMAL(10, 2);

    SELECT salary
    INTO v_current_salary
    FROM employees
    WHERE id = p_employee_id;

    SET v_max_salary =
        v_current_salary *
        (1 + p_max_raise_percentage / 100);

    IF p_new_salary > v_max_salary THEN
        SET p_new_salary = v_max_salary;
    END IF;

    IF p_new_salary < v_current_salary THEN
        SET p_new_salary = v_current_salary;
    END IF;
END //

DELIMITER ;

This example assumes the employee ID exists. A production procedure should explicitly handle missing employees, for example by defining an appropriate SQL exception handler.

12.2 Java Example

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;

public class SalaryRaiseProcedure {
    public static void main(String[] args) {
        String call = "{call CalculateSalaryRaise(?, ?, ?)}";

        try (Connection connection = DBConnection.getConnection();
             CallableStatement statement =
                 connection.prepareCall(call)) {

            statement.setInt(1, 1);
            statement.setBigDecimal(
                2, new BigDecimal("10.00")
            );

            statement.setBigDecimal(
                3, new BigDecimal("60000.00")
            );
            statement.registerOutParameter(3, Types.DECIMAL);

            statement.execute();

            BigDecimal adjustedSalary =
                statement.getBigDecimal(3);

            System.out.println(
                "Adjusted salary: " + adjustedSalary
            );

        } catch (SQLException e) {
            System.err.println("Salary calculation failed.");
            e.printStackTrace();
        }
    }
}

12.3 Explanation

• Parameter 1 supplies the employee ID.
• Parameter 2 supplies the maximum raise percentage.
• Parameter 3 supplies the proposed salary and is also registered as an output parameter.
• After execution, getBigDecimal(3) retrieves the adjusted salary.

Registering an INOUT parameter and setting its initial value are both necessary. The parameter index must match the order declared in the stored procedure.

13. Calling a Procedure That Returns Multiple Rows

A stored procedure may return a result set containing several rows. The Java application can iterate through the ResultSet just as it would with a normal SELECT query.

13.1 Create the Procedure

DELIMITER //

CREATE PROCEDURE GetEmployeesByDepartment(
    IN p_department VARCHAR(100)
)
BEGIN
    SELECT id, name, email, department, salary
    FROM employees
    WHERE department = p_department
    ORDER BY name;
END //

DELIMITER ;

13.2 Java Example

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DepartmentEmployees {
    public static void main(String[] args) {
        String call = "{call GetEmployeesByDepartment(?)}";

        try (Connection connection = DBConnection.getConnection();
             CallableStatement statement =
                 connection.prepareCall(call)) {

            statement.setString(1, "IT");

            boolean hasResultSet = statement.execute();

            if (hasResultSet) {
                try (ResultSet resultSet =
                         statement.getResultSet()) {

                    while (resultSet.next()) {
                        System.out.println(
                            resultSet.getInt("id") + " | " +
                            resultSet.getString("name") + " | " +
                            resultSet.getString("email")
                        );
                    }
                }
            }

        } catch (SQLException e) {
            System.err.println(
                "Unable to retrieve department employees."
            );
            e.printStackTrace();
        }
    }
}

execute() is useful when the procedure may return a result set, an update count, or a sequence of results. For procedures that reliably return a single result set, executeQuery() may also be suitable when supported by the driver and procedure behavior.

14. Understanding CallableStatement Methods

15. Calling a Stored Function

A stored function differs from a stored procedure because it returns a value. JDBC uses a return-value placeholder in the call syntax when invoking a stored function.

For example, a MySQL function might return a count or a calculated value:

CREATE FUNCTION DoubleValue(p_value INT)
RETURNS INT
DETERMINISTIC
RETURN p_value * 2;

A corresponding JDBC call can use the function escape syntax:

String call = "{? = call DoubleValue(?)}";

try (Connection connection = DBConnection.getConnection();
     CallableStatement statement =
         connection.prepareCall(call)) {

    statement.registerOutParameter(1, java.sql.Types.INTEGER);
    statement.setInt(2, 10);

    statement.execute();

    int result = statement.getInt(1);
    System.out.println("Result: " + result);
}

Stored-function syntax and permissions depend on the database. Some databases require a different SQL call form, so consult the database-specific documentation.

16. Handling Multiple Results

A stored procedure may return multiple result sets and update counts. In such cases, execute() followed by getResultSet(), getUpdateCount(), and getMoreResults() can be used to process the results in sequence.

boolean isResultSet = statement.execute();

while (true) {
    if (isResultSet) {
        try (ResultSet resultSet = statement.getResultSet()) {
            while (resultSet.next()) {
                System.out.println(
                    resultSet.getObject(1)
                );
            }
        }
    } else if (statement.getUpdateCount() == -1) {
        break;
    }

    isResultSet = statement.getMoreResults();
}

The exact results returned by a procedure depend on its SQL statements and the database driver. For advanced procedures, consult the JDBC driver documentation and ensure all results are processed or closed as required.

17. Exception Handling in CallableStatement

Stored procedure calls may fail because of incorrect procedure names, missing parameters, incompatible data types, insufficient privileges, SQL errors inside the procedure, or network problems.

17.1 Common SQLException Methods

17.2 Example

try (Connection connection = DBConnection.getConnection();
     CallableStatement statement =
         connection.prepareCall("{call GetEmployeeById(?)}")) {

    statement.setInt(1, 1);
    statement.execute();

} catch (SQLException e) {
    System.err.println("Procedure execution failed.");
    System.err.println("SQL state: " + e.getSQLState());
    System.err.println("Error code: " + e.getErrorCode());
    e.printStackTrace();
}

In production applications, use structured logging and avoid exposing credentials or sensitive database details to end users.

18. Common Errors and Troubleshooting

19. Security Best Practices

• Use parameter placeholders rather than concatenating user input into procedure-call strings.
• Grant application accounts only the privileges they need.
• Keep database credentials outside source code.
• Validate values before calling procedures.
• Use transactions where several changes must succeed together.
• Handle database errors without revealing sensitive details to end users.
• Review stored procedure permissions and whether procedures execute with invoker or definer privileges.
• Close CallableStatement, ResultSet, and Connection objects reliably.
• Do not assume stored procedures are automatically secure; review their SQL and authorization behavior.

20. Best Practices for Using CallableStatement

• Use descriptive procedure names that reflect their responsibilities.
• Keep parameter order and data types consistent between SQL and Java.
• Register all OUT parameters before execution.
• Set the initial values of IN and INOUT parameters correctly.
• Use execute() when the procedure may return different kinds of results.
• Use executeQuery() for calls that are known to return a single result set and are supported by the driver.
• Use try-with-resources for statements, result sets, and connections.
• Document procedure inputs, outputs, exceptions, and transaction expectations.
• Test procedures with valid inputs, missing records, boundary values, and invalid inputs.
• Check database-specific documentation for driver support and procedure syntax.

21. Mini Project: Employee Procedure Management

Build a Java application that uses stored procedures for employee database operations.

21.1 Project Requirements

• Create a stored procedure to retrieve an employee by ID.
• Create a procedure that returns an employee name through an OUT parameter.
• Create a procedure to retrieve employees by department.
• Create a procedure that calculates a proposed salary adjustment using an INOUT parameter.
• Call each procedure from Java using CallableStatement.
• Display returned values and result sets.
• Handle missing records and SQL exceptions.

21.2 Suggested Project Structure

EmployeeProcedureProject/
|
|-- src/
|   |-- DBConnection.java
|   |-- CallEmployeeById.java
|   |-- EmployeeNameProcedure.java
|   |-- DepartmentEmployees.java
|   |-- SalaryRaiseProcedure.java
|   |-- Main.java
|
|-- sql/
|   |-- create_procedures.sql
|
|-- pom.xml

21.3 Development Steps

1. Create the database and employees table.
2. Configure the JDBC driver dependency.
3. Create and test the stored procedures in the database.
4. Build the reusable database connection class.
5. Implement Java methods that call each stored procedure.
6. Test IN, OUT, and INOUT parameters.
7. Process result sets and output values.
8. Test missing records, invalid values, and permission errors.

22. Interview Questions and Answers

Q1. What is CallableStatement in JDBC?

Answer: CallableStatement is a JDBC interface used to call stored procedures and supported stored functions from Java applications.

Q2. Which interface does CallableStatement extend?

Answer: CallableStatement extends PreparedStatement.

Q3. How do you create a CallableStatement?

Answer: Call Connection.prepareCall() with a valid procedure-call string.

Q4. What is the difference between IN and OUT parameters?

Answer: IN parameters pass values to the procedure. OUT parameters return values from the procedure to the caller.

Q5. How do you register an OUT parameter?

Answer: Use registerOutParameter(index, sqlType) before executing the procedure.

Q6. How do you retrieve an OUT parameter?

Answer: Execute the procedure and then use the appropriate getter method, such as getString(), getInt(), or getBigDecimal().

Q7. What is an INOUT parameter?

Answer: It accepts an initial input value and returns a potentially modified value. In JDBC, set the value and register the same parameter as an output parameter.

Q8. What is the difference between execute() and executeQuery()?

Answer: execute() can handle a variety of result types. executeQuery() is used when a single ResultSet is expected and the driver supports that usage.

Q9. Can a stored procedure return multiple rows?

Answer: Yes. A procedure can return a result set, which Java can process using ResultSet methods.

Q10. Why should CallableStatement be closed?

Answer: Closing it releases JDBC resources. Try-with-resources is a convenient way to close it automatically.

23. Practice Exercises

1. Create a stored procedure that retrieves an employee using an IN parameter.
2. Call the procedure from Java and display the returned record.
3. Create a procedure that returns an employee's department through an OUT parameter.
4. Write a procedure that accepts an employee ID and returns the employee's salary.
5. Create a procedure that retrieves employees from a specified department.
6. Write an INOUT procedure that calculates a permitted salary adjustment.
7. Call a stored function from Java using the return-value placeholder syntax.
8. Test what happens when an invalid employee ID is supplied.
9. Handle a procedure that returns no matching rows.
10. Document the procedure signature, parameter types, and expected results for each example.

24. Quick Revision Checklist

• CallableStatement belongs to the java.sql package.
• It extends PreparedStatement.
• Connection.prepareCall() creates a CallableStatement.
• IN parameters pass values to the procedure.
• OUT parameters return values to Java.
• INOUT parameters accept an input and return an output.
• Register OUT parameters before executing the procedure.
• Use setter methods to supply IN parameter values.
• Use getter methods to retrieve output values.
• Use ResultSet to process returned rows.
• Use try-with-resources to close JDBC resources.
• Stored procedure syntax and capabilities vary by database system.

25. Official Documentation and Further Reading

• Oracle JDBC Tutorial: Using Stored Procedures
• Java CallableStatement API Documentation
• Java Connection Interface Documentation
• MySQL Connector/J Documentation

26. Conclusion

CallableStatement is an important JDBC interface for invoking stored procedures from Java applications. It supports passing input values, registering and retrieving output values, and processing result sets returned by database routines.

By understanding IN, OUT, and INOUT parameters, choosing the correct execution method, handling exceptions, and closing resources properly, developers can integrate Java applications with database-side business logic more effectively. Practice the examples with your chosen database and verify any database-specific syntax before using stored procedures in a production application.

Explore the course: Advanced Java Training at JustAcademy

Register for a demo: Register for a Course Demo

Visit the website: JustAcademy